<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Worklinkrecord extends Model
{
    protected $table = 'work_link_record_';
    protected $primaryKey = 'id';

    public function __construct(array $custb=[])
    {
        if (empty($custb)) {
            $this->table = 'work_link_record_'.(date("m",time())%4);
        } else {
            $this->table = 'work_link_record_'.$custb["tbnum"];
        }
    }

    public function creatWorkLinkRecordWeb($record_id,$kfid,$province,$city,$district,$system,$device,$browser,$ip,$code,$web_time,$clickid,$adid,$back_platfrom)
    {
        return self::insert(["record_id"=>$record_id,"kfid"=>$kfid,"province"=>$province,"city"=>$city,"district"=>$district,"system"=>$system,"device"=>$device,"browser"=>$browser,"ip"=>$ip,"code"=>$code,"web_time"=>$web_time,"clickid"=>$clickid,"adid"=>$adid,"back_platfrom"=>$back_platfrom,"created_at"=>$web_time]);
    }

    public function updateWorkLinkRecordWeb($record_id,$kefu_time,$qrcode_show_time,$qrcode_press_time,$qrcode_card_time,$qrcode_leave_time,$qrcode_id)
    {
        return self::where("record_id",$record_id)->update(["kefu_time"=>$kefu_time,"qrcode_show_time"=>$qrcode_show_time,"qrcode_press_time"=>$qrcode_press_time,"qrcode_card_time"=>$qrcode_card_time,"qrcode_leave_time"=>$qrcode_leave_time,"qrcode_id"=>$qrcode_id]);
    }

    public function creatWorkLinkRecordMini($record_id,$kfid,$province,$city,$system,$device,$browser,$ip,$code,$kefu_time,$qrcode_show_time,$qrcode_press_time,$qrcode_card_time,$qrcode_leave_time)
    {
        return self::insert(["record_id"=>$record_id,"kfid"=>$kfid,"province"=>$province,"city"=>$city,"system"=>$system,"device"=>$device,"browser"=>$browser,"ip"=>$ip,"code"=>$code,"kefu_time"=>$kefu_time,"qrcode_show_time"=>$qrcode_show_time,"qrcode_press_time"=>$qrcode_press_time,"qrcode_card_time"=>$qrcode_card_time,"qrcode_leave_time"=>$qrcode_leave_time,"created_at"=>$kefu_time]);
    }

    public function creatWorkLinkRecordBatch($linksRecords)
    {
        return self::insert($linksRecords);
    }

    public function updateWorkLinkRecordPressTime($record_id,$qrcode_press_time)
    {
        return self::where("record_id",$record_id)->update(["qrcode_press_time"=>$qrcode_press_time]);
    }

    public function updateWorkLinkRecordShowTime($record_id,$qrcode_show_time,$kefu_time)
    {
        return self::where("record_id",$record_id)->update(["qrcode_show_time"=>$qrcode_show_time,"kefu_time"=>$kefu_time]);
    }

    public function workLinkRecordLists($kfid,$qrcode_show_time,$qrcode_press_time,$qrcode_card_time,$date_start,$date_end,$code,$province)
    {
        $query = self::select($this->table.'.*','work_qrcode_img.name','work_qrcode_img.img_url')->leftJoin('work_qrcode_img', 'work_qrcode_img.id', '=', $this->table.'.qrcode_id')->where($this->table.'.kfid', $kfid);
        if ($qrcode_show_time == 1) $query->where($this->table.'.qrcode_show_time',">",1);
        if ($qrcode_show_time == 2) $query->where($this->table.'.qrcode_show_time',"=",1);
        if ($qrcode_press_time == 1) $query->where($this->table.'.qrcode_press_time',">",1);
        if ($qrcode_press_time == 2) $query->where($this->table.'.qrcode_press_time',"=",1);
        if ($qrcode_card_time == 1) $query->where($this->table.'.qrcode_card_time',">",1);
        if ($qrcode_card_time == 2) $query->where($this->table.'.qrcode_card_time',"=",1);
        if (!empty($code)) $query->where($this->table.'.code',$code);
        if (!empty($province)) $query->where($this->table.'.province',$province);
        if (!empty($date_start)) $query->whereBetween($this->table.'.created_at',[$date_start,$date_end]);
        return $query->orderBy("id","DESC")->paginate(25);
    }

    public function workLinkRecordListSample($kfid,$qrcode_show_time,$qrcode_press_time,$qrcode_card_time,$date_start,$date_end,$offset,$limit,$code,$province)
    {
        $query = self::select($this->table.'.*','work_qrcode_img.name','work_qrcode_img.img_url')->leftJoin('work_qrcode_img', 'work_qrcode_img.id', '=', $this->table.'.qrcode_id')->where($this->table.'.kfid', $kfid);
        if ($qrcode_show_time == 1) $query->where($this->table.".qrcode_show_time",">",1);
        if ($qrcode_show_time == 2) $query->where($this->table.".qrcode_show_time","=",1);
        if ($qrcode_press_time == 1) $query->where($this->table.".qrcode_press_time",">",1);
        if ($qrcode_press_time == 2) $query->where($this->table.".qrcode_press_time","=",1);
        if ($qrcode_card_time == 1) $query->where($this->table.".qrcode_card_time",">",1);
        if ($qrcode_card_time == 2) $query->where($this->table.".qrcode_card_time","=",1);
        if (!empty($code)) $query->where($this->table.'.code',$code);
        if (!empty($province)) $query->where($this->table.'.province',$province);
        if (!empty($date_start)) $query->whereBetween($this->table.'.created_at',[$date_start,$date_end]);
        return $query->offset($offset)->limit($limit)->orderBy("id","DESC")->get();
    }

    public function workLinkRecordListSampleCount($kfid,$qrcode_show_time,$qrcode_press_time,$qrcode_card_time,$date_start,$date_end,$code,$province)
    {
        $query = self::select($this->table.'.*','work_qrcode_img.name','work_qrcode_img.img_url')->leftJoin('work_qrcode_img', 'work_qrcode_img.id', '=', $this->table.'.qrcode_id')->where($this->table.'.kfid', $kfid);
        if ($qrcode_show_time == 1) $query->where($this->table.".qrcode_show_time",">",1);
        if ($qrcode_show_time == 2) $query->where($this->table.".qrcode_show_time","=",1);
        if ($qrcode_press_time == 1) $query->where($this->table.".qrcode_press_time",">",1);
        if ($qrcode_press_time == 2) $query->where($this->table.".qrcode_press_time","=",1);
        if ($qrcode_card_time == 1) $query->where($this->table.".qrcode_card_time",">",1);
        if ($qrcode_card_time == 2) $query->where($this->table.".qrcode_card_time","=",1);
        if (!empty($code)) $query->where($this->table.'.code',$code);
        if (!empty($province)) $query->where($this->table.'.province',$province);
        if (!empty($date_start)) $query->whereBetween($this->table.'.created_at',[$date_start,$date_end]);
        return $query->count($this->table.".id");
    }

    public function getWorkLinkRecordDetial($record_id)
    {
        return self::where("record_id", $record_id)->first();
    }

    public function getSystemDatas($kfid)
    {
        return self::select(DB::raw('count(id) as system_count, system'))
                     ->where('kfid', $kfid)
                     ->distinct('ip')
                     ->groupBy('system')
                     ->get();
    }

    public function getBrowserDatas($kfid)
    {
        return self::select(DB::raw('count(id) as browser_count, browser'))
                     ->where('kfid', $kfid)
                     ->distinct('ip')
                     ->groupBy('browser')
                     ->get();
    }

    public function getDeviceDatas($kfid)
    {
        return self::select(DB::raw('count(id) as device_count, device'))
                     ->where('kfid', $kfid)
                     ->distinct('ip')
                     ->groupBy('device')
                     ->get();
    }

    public function getProvinceDatas($kfid)
    {
        return self::select(DB::raw('count(id) as province_count, province'))
                     ->where('kfid', $kfid)
                     ->distinct('ip')
                     ->groupBy('province')
                     ->get();
    }

    public function getDailyCount($kfid,$keyword,$start_time,$end_time)
    {
        return self::where('kfid', $kfid)
                     ->where($keyword,">",1)
                     ->whereBetween("created_at",[$start_time,$end_time])
                     ->count();
    }

    public function getDailyDirectCount($kfid,$start_time,$end_time)
    {
        return self::where('kfid', $kfid)
                     ->where("web_time",1)
                     ->where("qrcode_show_time",">",1)
                     ->whereBetween("created_at",[$start_time,$end_time])
                     ->count();
    }

    public function getDefaultDatas($kfid,$start_time,$end_time,$datebatch)
    {
        if ($datebatch == "day") {
            $unit = "%Y-%m-%d";
        } else {
            $unit = "%H";
        }
        return self::select(DB::raw('count(id) as count, DATE_FORMAT(FROM_UNIXTIME(created_at),"'.$unit.'") as `date`,status'))
                     ->where('kfid', $kfid)
                     ->whereBetween('created_at', [$start_time,$end_time])
                     ->distinct('ip')
                     ->groupBy('date')
                     ->groupBy('status')
                     ->get();
    }

    public function getKfRecordHour($kfid,$keyword,$created_at)
    {
        return DB::select('select count("id") as count,FROM_UNIXTIME(created_at,"%H") as hour from '.$this->table.' where kfid = "'.$kfid.'" and '.$keyword.' > 1 and FROM_UNIXTIME(created_at,"%Y-%m-%d") = "'.$created_at.'" group by hour');
    }

    public function rdirectKfRecordHour($kfid,$created_at)
    {
        return DB::select('select count("id") as count,FROM_UNIXTIME(created_at,"%H") as hour from '.$this->table.' where kfid = "'.$kfid.'" and web_time = 1 and kefu_time > 1 and FROM_UNIXTIME(created_at,"%Y-%m-%d") = "'.$created_at.'" group by hour');
    }

    public function getLinkExportCount($kfid,$start_time,$end_time)
    {
        return self::where('kfid',$kfid)
                     ->whereBetween("created_at",[$start_time,$end_time])
                     ->count();
    }

    public function updateWorkLinkQrcodeShow($record_id,$qrcode_show_time,$qrcode_id)
    {
        return self::where("record_id",$record_id)->update(["qrcode_show_time"=>$qrcode_show_time,"qrcode_id"=>$qrcode_id]);
    }

    public function updateWorkLinkQrcodePress($record_id,$qrcode_press_time)
    {
        return self::where("record_id",$record_id)->update(["qrcode_press_time"=>$qrcode_press_time]);
    }

    public function updateWorkLinkClickTime($record_id,$click_time)
    {
        return self::where("record_id",$record_id)->update(["click_time"=>$click_time]);
    }

    public function updateWorkLinkKefuTime($record_id,$kefu_time,$send_status)
    {
        return self::where("record_id",$record_id)->update(["kefu_time"=>$kefu_time,"send_status"=>$send_status]);
    }

    public function updateWorkLinkQrcodePressCard($record_id,$qrcode_card_time)
    {
        return self::where("record_id",$record_id)->update(["qrcode_card_time"=>$qrcode_card_time]);
    }

    public function getWorkLinkRecordByRid($record_id)
    {
        return self::select("browser","clickid","callback_status")->where("record_id",$record_id)->first();
    }

    public function updateWorkLinkRecordCallback($record_id,$paramas)
    {
        return self::where("record_id",$record_id)->update($paramas);
    }
}
